#!/usr/bin/env python

from google.appengine.ext import webapp
from google.appengine.api import users
from google.appengine.ext.webapp import util
from google.appengine.api import mail
import parser, vdb, ui
import logging, datetime

cat_row = '''
        <td>
            <a href="/cat?s=s1,date,o1,d&id=%s">%s<a>
        </td>
        <td>%.2f</td>
        <td>%.2f</td>
        <td>%s</td>
'''

row_template = '''
    <td><a href="/view?id=%d">%d<a></td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>
    <td>%s</td>\n'''

class MainHandler(webapp.RequestHandler):
    def get(self):
        pgstate = ui.parse_state(self.request.get('s'))
        vdb.ns_set()
        text = ui.get_menu()
        days = float((datetime.datetime.now() - datetime.datetime(2011, 7, 30)).days)

        q = vdb.stat_get_all()
        lines = []
        lines.append(('', 'atag', 'total', 'average', 'updated'))
        for item in q:
            updated = item.updated
            if item.updated is None:
                updated = datetime.datetime(1900, 1, 1)
            lines.append((item.atag, item.atag, item.amount,
                          item.amount * 30.42 / days, updated))
        text += ui.format_table(1, lines, pgstate, cat_row) + '<br/>'
       
        q = vdb.msg_get_all()
        q.filter('atag ==', '')
        list = []
        list.append(('id', '', 'pid', 'b',
                     'date', 'place', 'name', 'refnum', 
                     'atag', 'amount', 'parsed'))
        for item in q:
            pid = ''
            body = ''
            date = item.date
            if item.body is None:
                body = 'N'
            if not item.parent_id  is None:
                pid = item.parent_id
            samount = ''
            if not item.amount is None:
                samount = '%.2f' % item.amount
            if item.date is None:
                date = datetime.datetime(1900, 1, 1)
                
            list.append((item.key().id(), item.key().id(), pid, body,
                         date, item.place, item.name, item.refnum, 
                         item.atag, item.amount, ['', 'Y'][item.parsed]))

        text += ui.format_table(2, list, pgstate, row_template)
        self.response.out.write(ui.format_page('main', text))

class ViewHandler(webapp.RequestHandler):
    def get(self):
        vdb.ns_set()
        text = ui.get_menu()
        msg = vdb.msg_get(int(self.request.get('id')))
        if not msg.parent_id is None:
            msg = vdb.msg_get(int(msg.parent_id))
        text += '<pre>' + str(msg.subject) + '\n==========================\n' + str(msg.body) + '</pre>'
        self.response.out.write(ui.format_page('viewer', text))

class ViewcatHandler(webapp.RequestHandler):
    def get(self):
        pgstate = ui.parse_state(self.request.get('s'))
        cat = self.request.get('id')
        pgstate.perm_params['id'] = cat
        vdb.ns_set()
        text = ui.get_menu()
        q = vdb.msg_get_all()
        if cat != 'total':
            q.filter('atag =', cat)
        else:
            q.filter('atag !=', '')
        list = []
        list.append(('id', '', 'pid', 'b',
                     'date', 'place', 'name', 'refnum', 
                     'atag', 'amount', 'parsed'))
        total = 0.0
        for item in q:
            pid = ''
            body = ''
            if not item.parent_id  is None:
                pid = item.parent_id
            if item.body is None:
                body = 'N'
            list.append((int(item.key().id()), int(item.key().id()), pid, body, 
                         item.date, item.place, item.name, item.refnum, 
                         item.atag, item.amount, ['', 'Y'][item.parsed]))
            if item.atag != 'chase' and not item.amount is None:
                total += item.amount
        text += 'total: %.2f\n' % total
        if users.is_current_user_admin():
            text += '<input type="hidden" name="cat" value="%s" />\n' % cat
            text += '<input type="submit" name="recalc" value="Recalculate" />\n<br><br>\n'
        text += ui.format_table(1, list, pgstate, row_template)
        self.response.out.write(ui.format_page('viewer', text))

class CsvHandler(webapp.RequestHandler):
    def get(self):
        vdb.ns_set()
        q = vdb.msg_get_all()
        list = []
        list.append(('id', '', 'pid', 'b',
                     'date', 'place', 'name', 'refnum', 
                     'atag', 'amount', 'parsed'))
        for item in q:
            pid = ''
            body = ''
            date = item.date
            if item.body is None:
                body = 'N'
            if not item.parent_id  is None:
                pid = item.parent_id
            samount = ''
            if not item.amount is None:
                samount = '%.2f' % item.amount
            if item.date is None:
                date = datetime.datetime(1900, 1, 1)
                
            list.append((item.key().id(), item.key().id(), pid, body,
                         date, item.place.replace(',', ''), item.name, item.refnum, 
                         item.atag, item.amount, ['', 'Y'][item.parsed]))

        text = ''
        for row in list:
            text += ','.join([str(s) for s in row]) + '\n'
        self.response.headers['Content-Type'] = 'application/ms-excel'
        self.response.headers['Content-Transfer-Encoding'] = 'Binary'
        self.response.headers['Content-disposition'] = 'attachment; filename="transactions.csv"'
        self.response.out.write(text)

def get_details_html(req):
    from sets import Set

    sum_per_month = {}
    sum_per_tag = {}
    sum_per_tag_lw = {}
    sum_per_month_tag = {}
    month_tags = []
    months = []
    tags = Set()

    text = '<html>\n<body>\n'
    today = datetime.date.today()
    min_date = today - datetime.timedelta(days = 93)
    this_week = today - datetime.timedelta(days = -today.weekday())
    q = vdb.msg_get_all()

    q.filter('date >=', min_date)
    q.filter('parsed ==', True)
  
    q = sorted(q, key=lambda row: row.date, reverse = True)
    sum = 0
    for item in q:
        if item.atag in ['bilz', 'chase', 'apt', 'filtered']:
            continue

        if not item.month in sum_per_month:
            sum_per_month[item.month] = item.amount
            months.append(item.month)
        else:
            sum_per_month[item.month] += item.amount

        if not item.atag in sum_per_tag:
            sum_per_tag[item.atag] = item.amount
        else:
            sum_per_tag[item.atag] += item.amount
            tags.add(item.atag)

        mtag = str(item.month) + ' ' + str(item.atag)
        day = str(item.date.day)

        if not mtag in sum_per_month_tag:
            sum_per_month_tag[mtag] = [item.amount, '&nbsp;&nbsp;<span style="display: inline-block; width: 20px; text-align: right;">%s</span>&nbsp;&nbsp;<span style="display: inline-block; width: 160px;">%s</span>&nbsp;&nbsp;<span style="display: inline-block; width: 80px; text-align: right;">%.2f</span><br>\n' % (day, item.place.lower(), item.amount)]
            month_tags.append(mtag)
        else:
            sum_per_month_tag[mtag][0] += item.amount
            sum_per_month_tag[mtag][1] += '&nbsp;&nbsp;<span style="display: inline-block; width: 20px; text-align: right;">%s</span>&nbsp;&nbsp;<span style="display: inline-block; width: 160px;">%s</span>&nbsp;&nbsp;<span style="display: inline-block; width: 80px; text-align: right;">%.2f</span><br>\n' % (day, item.place.lower(), item.amount)

    months = months[:3]
    text += '<table style="border: solid 1px gray;">\n<tr>\n'
    text += '  <th style="text-align: left; background-color: lightsteelblue;">&nbsp;</th>\n'
    for m in months:
        text += '  <th style="text-align: left; background-color: lightsteelblue;">%s</th>\n' % m
    text += '</tr>\n'

    text += '<tr><th style="text-align: left; background-color: lightsteelblue;">total</th>'
    for m in months:
        text += '<th style="text-align: right; background-color: lightsteelblue;">%.0f</th>' % sum_per_month[m]
    text += '</tr>\n'

    for t in tags:
        text += '<tr><td>%s</td>' % t
        for m in months:
            mt = m + ' ' + t
            if mt in sum_per_month_tag:
                text += '<td style="text-align: right;">%.0f</td>' % sum_per_month_tag[mt][0]
            else:
                text += '<td>&nbsp;</td>'
        text += '</tr>\n'

    text += '</table>\n'

    text += '<h4>Itemized per tag per month:</h4>\n'
    text += '<table style="border: solid 1px gray; ">\n'
    for k in sorted(sum_per_month_tag.iterkeys(), reverse = True):
        text += '<tr><th style="text-align: left; background-color: lightsteelblue;">%s</th><th style="text-align: left; background-color: lightsteelblue;">%s</th></tr><tr><td colspan="2">%s</td></tr>\n' % (k, sum_per_month_tag[k][0], sum_per_month_tag[k][1])
    text += '</table>\n'
    text += '</body>\n</html>\n'

    return text

def get_details(req):
    from sets import Set

    sum_per_month = {}
    sum_per_tag = {}
    sum_per_tag_lw = {}
    sum_per_month_tag = {}
    month_tags = []
    months = []
    tags = Set()

    text = ''
    today = datetime.date.today()
    min_date = today - datetime.timedelta(days = 93)
    this_week = today - datetime.timedelta(days = -today.weekday())
    q = vdb.msg_get_all()

    q.filter('date >=', min_date)
    q.filter('parsed ==', True)
  
    q = sorted(q, key=lambda row: row.date, reverse = True)
    sum = 0
    for item in q:
        if item.atag in ['bilz', 'chase', 'apt', 'filtered']:
            continue

        if not item.month in sum_per_month:
            sum_per_month[item.month] = item.amount
            months.append(item.month)
        else:
            sum_per_month[item.month] += item.amount

        if not item.atag in sum_per_tag:
            sum_per_tag[item.atag] = item.amount
        else:
            sum_per_tag[item.atag] += item.amount
            tags.add(item.atag)

        mtag = str(item.month) + ' ' + str(item.atag)

        if not mtag in sum_per_month_tag:
            sum_per_month_tag[mtag] = [item.amount, '\n                   %s %5.2f' % (item.place.ljust(20), item.amount)]
            month_tags.append(mtag)
        else:
            sum_per_month_tag[mtag][0] += item.amount
            sum_per_month_tag[mtag][1] += '\n                   %s %5.2f' % (item.place.ljust(20), item.amount)

    months = months[:3]
    text += ' '.ljust(12) + ' '
    for m in months:
        text += m.rjust(12) + ' '
    text += '\n'
    text += ' '.ljust(12) + ' '
    for m in months:
        text += '-'.ljust(12, '-') + ' '
    text += '\n'
    for t in tags:
        text += t.ljust(12)
        for m in months:
            mt = m + ' ' + t
            if mt in sum_per_month_tag:
                text += ' %12.0f' % sum_per_month_tag[mt][0]
            else:
                text += ' '.ljust(12) + ' '
        text += ' \n'
    text += ' '.ljust(12) + ' '
    for m in months:
        text += '-'.ljust(12, '-') + ' '

    text += ' \n'
    text += ' '.ljust(12)
    for m in months:
        text += ' %12.0f' % sum_per_month[m]

    text += '\n\n\n'

    text += 'Itemized per tag per month:\n=============================\n\n'
    for k in sorted(sum_per_month_tag.iterkeys(), reverse = True):
        text += '%s %s %s\n' % (k, sum_per_month_tag[k][0], sum_per_month_tag[k][1])

    return text

class SendMailHandler(webapp.RequestHandler):
    def get(self):
        vdb.ns_set_admin()
        
        text = get_details(self)
        text += '\n\nhttps://voismailer.appspot.com/details\n'
        html_text = get_details_html(self)
        html_text += '\n\n<a href="https://voismailer.appspot.com/details">details</a>\n'

        
        mail.send_mail(sender = 'voismailer <mailer@voismailer.appspotmail.com>',
              to = ['voilokov@gmail.com'],
              subject = 'expences',
              body = text,
              html = html_text)
        self.response.headers['Content-Type'] = 'text/html'
        self.response.out.write(html_text)
 
class DetailsHandler(webapp.RequestHandler):
    def get(self):
        from sets import Set

        sum_per_month = {}
        sum_per_tag = {}
        sum_per_month_tag = {}
        month_tags = []
        months = []
        tags = Set()

        vdb.ns_set()
        text = ''
        year_ago = datetime.date.today() - datetime.timedelta(days = 365)
        q = vdb.msg_get_all()
        q.filter('parsed ==', True)
        q = sorted(q, key=lambda row: row.date, reverse = True)
        sum = 0
        for item in q:
            if item.date.date() < year_ago:
                continue
            if item.atag in ['bilz', 'chase', 'apt', 'filtered']:
                continue

            if not item.month in sum_per_month:
                sum_per_month[item.month] = item.amount
                months.append(item.month)
            else:
                sum_per_month[item.month] += item.amount

            if not item.atag in sum_per_tag:
                sum_per_tag[item.atag] = item.amount
            else:
                sum_per_tag[item.atag] += item.amount
                tags.add(item.atag)

            mtag = str(item.month) + ' ' + str(item.atag)

            if not mtag in sum_per_month_tag:
                sum_per_month_tag[mtag] = [item.amount, '\n                   %s %5.2f' % (item.place.ljust(20), item.amount)]
                month_tags.append(mtag)
            else:
                sum_per_month_tag[mtag][0] += item.amount
                sum_per_month_tag[mtag][1] += '\n                   %s %5.2f' % (item.place.ljust(20), item.amount)

        months = months[:6]
        text += ' '.ljust(12) + ' '
        for m in months:
            text += m.rjust(12) + ' '
        text += '\n'
        text += ' '.ljust(12) + ' '
        for m in months:
            text += '-'.ljust(12, '-') + ' '
        text += '\n'
        for t in tags:
            text += t.ljust(12)
            for m in months:
                mt = m + ' ' + t
                if mt in sum_per_month_tag:
                    text += ' %12.0f' % sum_per_month_tag[mt][0]
                else:
                    text += ' '.ljust(12) + ' '
            text += ' \n'
        text += ' '.ljust(12) + ' '
        for m in months:
            text += '-'.ljust(12, '-') + ' '

        text += ' \n'
        text += ' '.ljust(12)
        for m in months:
            text += ' %12.0f' % sum_per_month[m]

        text += '\n\n\n'

#        for k in sorted(sum_per_month.iterkeys(), reverse = True):
#            text += '%s %5.0f\n' % (k.ljust(10), sum_per_month[k])
#        text += '\n'
#        for k in sorted(sum_per_tag.iterkeys()):
#            text += '%s %5.0f\n' % (k.ljust(10), sum_per_tag[k])
        text += 'Itemized per tag per month:\n=============================\n\n'
        for k in sorted(sum_per_month_tag.iterkeys(), reverse = True):
            text += '%s %s %s\n' % (k, sum_per_month_tag[k][0], sum_per_month_tag[k][1])

        self.response.headers['Content-Type'] = 'text/plain'
        self.response.out.write(text)
        

def main():
    application = webapp.WSGIApplication([
        ('/', MainHandler),
        ('/cat', ViewcatHandler),
        ('/csv', CsvHandler),
        ('/mail', SendMailHandler),
        ('/view', ViewHandler),
        ('/details', DetailsHandler)
        ], debug=True)
    util.run_wsgi_app(application)

if __name__ == '__main__':
    main()

